package com.cntcdt.api.db.dao;

import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.cntcdt.api.db.dao.base.CheckHistoryDaoBase;
import com.cntcdt.api.db.model.CheckHistoryModel;
import com.cntcdt.api.utils.EmojiEncodeUtils;
import com.cntcdt.api.utils.LogUtils;
import com.cntcdt.api.utils.ReflectUtils;
import com.coola.jutil.sql.DBEngine;
import com.coola.jutil.sql.DBFactory;

/**
 * 这是cdt_check_history的Dao实现类
 */
public class CheckHistoryDao extends CheckHistoryDaoBase {
	/**
	 * CheckHistoryDao对象
	 */
	private static CheckHistoryDao instance = new CheckHistoryDao();

	/**
	 * 默认构造方法
	 */
	private CheckHistoryDao() {
	}

	/**
	 * 获得对象实例
	 */
	public static CheckHistoryDao getInstance() {
		return instance;
	}

	/**
	 * 插入一条数据
	 * 
	 * @param model
	 *            UserModel对象
	 * @return 返回插入影响行数
	 */
	public int insertAny(CheckHistoryModel model) {
		String name = EmojiEncodeUtils.filter2CharUnicode(model.getName());
		DBEngine localEngine = DBFactory.getKeepConnDBEngine("main");
		StringBuffer sql = new StringBuffer("INSERT INTO " + TABLE + " (");
		StringBuffer stmts = new StringBuffer(" values(");
		List<Object> sqlArgs = new ArrayList<Object>();
		Field[] superfields = model.getClass().getSuperclass().getDeclaredFields();
		// Field[] fields = userModel.getClass().getDeclaredFields();
		for (Field field : superfields) {
			if (field.getName().equals("serialVersionUID") || field.getName().equals("id")) {
				continue;
			}
			if (ReflectUtils.getter(model, field.getName()) != null) {
				sql.append(" " + field.getName() + ",");
				stmts.append("?,");
				if (field.getName().equals("name")) {
					sqlArgs.add(name);
				} else {
					sqlArgs.add(ReflectUtils.getter(model, field.getName()));
				}
			}
		}

		String sqlstr = sql.toString();
		sqlstr = sqlstr.substring(0, sqlstr.length() - 1);
		sqlstr = sqlstr + ")";

		String stmtstr = stmts.toString();
		stmtstr = stmtstr.substring(0, stmtstr.length() - 1);
		stmtstr = stmtstr + ")";

		LogUtils.log.info(sqlstr + stmtstr + "         " + sqlArgs);

		Object[] paramObj = sqlArgs.toArray();
		int id = 0;
		try {
			String tSql = sqlstr + stmtstr;
			id = localEngine.executeUpdate(tSql, paramObj);
			ResultSet rs = localEngine.executeQuery("select last_insert_id() as id");
			if (rs.next()) {
				id = rs.getInt("id");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			localEngine.close();
		}
		return id;
	}

	/**
	 * 更新操作，自动根据userModel中已经设置的字段进行更新 必须要设置userModel.id属性 和 其他任意一个属性
	 * 
	 * 返回更新结果
	 */
	public boolean updateAny(CheckHistoryModel model) {
		String name = EmojiEncodeUtils.filter2CharUnicode(model.getName());
		StringBuffer sql = new StringBuffer("update  " + TABLE + " set ");
		List<Object> sqlArgs = new ArrayList<Object>();
		Field[] superfields = model.getClass().getSuperclass().getDeclaredFields();
		// Field[] fields = userModel.getClass().getDeclaredFields();
		for (Field field : superfields) {
			if (field.getName().equals("serialVersionUID") || field.getName().equals("id")) {
				continue;
			}
			if (ReflectUtils.getter(model, field.getName()) != null) {
				sql.append(" " + field.getName() + "=? ,");
				if (field.getName().equals("name")) {
					sqlArgs.add(name);
				} else {
					sqlArgs.add(ReflectUtils.getter(model, field.getName()));
				}
			}
		}

		String sqlstr = sql.toString();
		sqlstr = sqlstr.substring(0, sqlstr.length() - 1);
		sqlstr = sqlstr + " where id=?";
		sqlArgs.add(ReflectUtils.getter(model, "id"));
		LogUtils.log.info(sqlstr);
		LogUtils.log.info(sqlArgs.toString());

		Object[] paramObj = sqlArgs.toArray();
		try {
			return writeDBEngine.executeUpdate(sqlstr, paramObj) > 0;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * 查询指定日期内的数据
	 * 
	 * @param terminal
	 * @param yesterdayTime
	 * @param todayTime
	 * @param sortTime
	 * @param terminalList
	 * @return
	 */
	public List<CheckHistoryModel> findByTimeStrAndSortTime(String terminal, Date yesterdayTime, Date todayTime,
			long sortTime, String terminalList) {
		List<CheckHistoryModel> modelList = new ArrayList<CheckHistoryModel>();
		if (terminalList.equals("")) {
			String sql = "SELECT * FROM " + TABLE
					+ " where deviceTerminal=? and returnTime >=? and returnTime<? and sortTime < ? and type = 2  order by sortTime desc limit 20";
			modelList = queryModelList(sql, new Object[] { terminal, yesterdayTime, todayTime, sortTime });
		} else {
			String sql = "SELECT * FROM " + TABLE + " where deviceTerminal in (" + terminalList
					+ ") and returnTime >=? and returnTime<? and sortTime < ? and type = 2  order by sortTime desc limit 20";
			modelList = queryModelList(sql, new Object[] { yesterdayTime, todayTime, sortTime });
		}
		return modelList;
	}

	/**
	 * 查询指定日期内充电宝的购买数据
	 * 
	 * @param terminal
	 * @param yesterdayTime
	 * @param todayTime
	 * @param sortTime
	 * @param terminalList
	 * @return
	 */
	public List<CheckHistoryModel> findCDBByTimeAndSortTime(String terminal, Date yesterdayTime, Date todayTime,
			long sortTime, String terminalList) {
		List<CheckHistoryModel> modelList = new ArrayList<CheckHistoryModel>();
		if (terminalList.equals("")) {
			String sql = "SELECT * FROM " + TABLE
					+ " where deviceTerminal=? and returnTime >=? and returnTime<? and sortTime < ? and type = 9  order by sortTime desc limit 20";
			modelList = queryModelList(sql, new Object[] { terminal, yesterdayTime, todayTime, sortTime });
		} else {
			String sql = "SELECT * FROM " + TABLE + " where deviceTerminal in (" + terminalList
					+ ") and returnTime >=? and returnTime<? and sortTime < ? and type = 9  order by sortTime desc limit 20";
			modelList = queryModelList(sql, new Object[] { yesterdayTime, todayTime, sortTime });
		}
		return modelList;
	}

	/**
	 * 根据充电宝编号分页查询租借记录
	 * 
	 * @param terminal
	 * @param sortTime
	 * @return
	 */
	public List<CheckHistoryModel> findCDBRentHistoryByTerminal(String terminal, int sortTime) {
		sortTime = sortTime * 20;
		List<CheckHistoryModel> modelList = new ArrayList<CheckHistoryModel>();
		String sql = "SELECT * FROM " + TABLE + " where deviceTerminal = ?  order by addTime desc limit ?,20";
		modelList = queryModelList(sql, new Object[] { terminal, sortTime });
		return modelList;
	}

	/**
	 * 根据充电宝编号分页查询
	 * 
	 * @param cdb
	 * @param sortTime
	 * @return
	 */
	public List<CheckHistoryModel> findByCDB(String cdb, int sortTime) {
		sortTime = sortTime * 20;
		// String sql = "SELECT * FROM " + TABLE + " where cdb like ? and type
		// in (1,2) order by addTime desc limit ?,20";
		String sql = "SELECT * FROM " + TABLE + " where cdb like ? order by addTime desc limit ?,20";
		List<CheckHistoryModel> modelList = queryModelList(sql, new Object[] { cdb, sortTime });
		return modelList;
	}

	/**
	 * 根据用户Id分页查询租借记录
	 * 
	 * @param userId
	 * @param sortTime
	 * @return
	 */
	public List<CheckHistoryModel> findCDBRentHistoryByUserId(String userId, int sortTime) {
		sortTime = sortTime * 20;
		List<CheckHistoryModel> modelList = new ArrayList<CheckHistoryModel>();
		String sql = "SELECT * FROM " + TABLE + " where userId = ?  order by addTime desc limit ?,20";
		modelList = queryModelList(sql, new Object[] { userId, sortTime });
		return modelList;
	}

	/**
	 * 查询借出的充电宝
	 * 
	 * @param cdb
	 * @return
	 */
	public CheckHistoryModel findByLendCDB(String cdb) {
		String sql = "SELECT * FROM " + TABLE + " where cdb like ? and type = 1";
		Object[] paramObjs = { "%"+cdb };
		List<CheckHistoryModel> modelList = this.queryModelList(sql, paramObjs);
		if (modelList.size() > 0) {
			return modelList.get(0);
		}
		return null;
	}

}
